﻿using System;
using System.Data;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using MySql.Data.MySqlClient;
using MailClient.Mail;

namespace MailClient.Data
{
    public enum ProjectStatus
    {
        /// <summary>
        /// For Active trident stores
        /// </summary>
        Active,
        /// <summary>
        /// For Dropped trident stores
        /// </summary>
        Dropped
    }

    public enum StoresType
    {
        Expansion,
        LandbankedNew,
        LandbankedTakeover,
        New,
        OnSiteRelocation,
        Relocation,
        RemodelC,
        Takeover,
        Undefined
    }

    /// <summary>
    /// Does the SQL lookups
    /// </summary>
    class Query
    {
        private System.Data.DataSet _scheduledata;

        protected DataSet ScheduleData
        {
            get { return _scheduledata; }
            set { _scheduledata = value; }
        }

        private DataSet _contactsdata;
        protected DataSet ContactsData
        {
            get { return _contactsdata; }
            set { _contactsdata = value; }
        }

        public Query()
        {
            //MySqlConn _con = new MySqlConn();
            //_con.Connect();
            //_con.LoadCategories();
            //_con.LoadCompanies();
            //_con.LoadContacts();
            //_con.LoadJobRoles();
            //_con.LoadProjectContacts();
            //_con.LoadProjects();
            //_con.Disconnect();
            //_scheduledata = _con.Data;
            _scheduledata = MailClient.Mail.Connections.connections.Schedule;
            _contactsdata = MailClient.Mail.Connections.connections.Contacts;

           
            foreach (DataRow row in _contactsdata.Tables["contacts"].Select())
            {
                row["FullName"] = row["first_name"].ToString() + ' ' + row["last_name"].ToString();
            }
        }

        /// <summary>
        /// Loads tables from database specifed in config file
        /// </summary>
        /// <param name="Table">table name to get</param>
        /// <returns></returns>
        protected System.Data.DataTable GetDataTable(string Table)
        {
        	#if!DEBUG
            Stream s = File.OpenRead(Properties.Settings.Default.CONFIGPATH);
            #else
            Stream s = File.OpenRead(Properties.Settings.Default.CONFIGPATHTEST);
            #endif
            BinaryFormatter b = new BinaryFormatter();
            ConfigLibrary.Config config = (ConfigLibrary.Config)b.Deserialize(s);
            s.Close();

            string username;
            string host;
            string pass;
            string database;
            int port;
            config.GetSqlInfo(out host, out username, out pass, out database, out port);

            MySqlConnection Scon = new MySqlConnection("Database=" + database + ";Data Source=" + 
                                                       host + ";User Id=" + username + ";Password=" + pass +
                                                       ";Port=" + port.ToString());
            Scon.Open();

            DataSet data = new DataSet();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter sqladapter = new MySqlDataAdapter();

            cmd.CommandText = "Select * from " + Table;
            cmd.Connection = Scon;
            sqladapter.SelectCommand = cmd;
            sqladapter.TableMappings.Add("Table", Table);
            sqladapter.FillSchema(data, SchemaType.Source, Table);
            sqladapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            sqladapter.Fill(data, Table);
            cmd.ExecuteNonQuery();
            Scon.Close();
            return data.Tables[0];
        }

        /// <summary>
        /// Uses the Database define in the current config file: currently 'Schedule'
        /// </summary>
        /// <param name="Data"></param>
        /// <param name="TableName"></param>
        protected void UpdateSQLTable(DataTable Data, string TableName, string Database)
        {
            MailClient.Mail.Connections.connections.UpdateSQL(Data, TableName, Database);
        }

        /// <summary>
        /// Returns a  data row
        /// </summary>
        /// <param name="q"></param>
        /// <returns></returns>
        public System.Data.DataRow[] TryQuery(JobType type, string q)
        {
            // trim the string
            q = q.Trim();

            if (type == JobType.Person)
            {
                /* if this is really a person, there should be a name
                 * test for space characters.
                 * if there is no " " then we'll need to try both first and last names
                 * if there is 1 " " then we've got a first name last name situation
                 * if there is 2 " " then we've probably got a middle name in there or maybe a suffix i.e. Jr.
                 * we shouldn't have "," since are already removed and people usually don't 
                 * write lastname, firstname. 
                 * filter expression needs to look like ='name'
                 */
                //string f = q.Word(0, " ").Trim();

                //    q = q.Remove(0, f.Length).Trim();
                //    f = "first_name='" + f + "'";
                //    q = "last_name='" + q + "'";
                //    f += " AND " + q;
                System.Data.DataTable table = _contactsdata.Tables["contacts"];
                //    {
                //        System.Data.DataRow[] rows = table.Select(f);
                //        return rows;
                //    }

                q = "FullName LIKE '*" + q + "*'";

                System.Data.DataRow[] rows = table.Select(q);
                return rows;


            }
            else if (type == JobType.Store)
            {
                /* if this is a store, there should be either a store number or a city
                 * but there might be both.  it is possible to have a squence number as well.
                 * we don't need a state. besides it'll be broken off becuase of the ","
                 * Walmart standard is ####-### city
                 * lets follow this and enforce it for sorting purposes.
                 */

                System.Data.DataTable table = _contactsdata.Tables["projects"];

                string f = q.Word(0, " ").Trim();
                q = q.Remove(0, f.Length).Trim();
                // f should be a number now. it might be #### or ####-## or ####.## or #######
                f = f.Replace('-', '.');
                float num;
                if (float.TryParse(f, out num))
                {
                    string[] qa = f.Split(new char[] { '.' });
                    f = qa[0];
                    f = "str_num='" + f + "'";
                    if (qa.Length > 1) f += " AND " + "str_seq LIKE '*" + qa[1] + "'";
                    f += " AND " + "str_city LIKE '*" + q + "*'";

                }
                else
                {// there was no store number
                    f = f.Replace(",", "");
                    f += " " + (q.Length == 2 ? null : q);
                    f = "str_city LIKE '*" + f + "*'";
                    f += (q.Length == 2 ? " AND str_st='" + q + "'" : null);
                }
                System.Data.DataRow[] rows = table.Select(f);
                return rows;
            }

            return null;
        }

        /// <summary>
        /// Returns rows from the trident schedule
        /// </summary>
        /// <param name="store_number"></param>
        /// <param name="store_seq"></param>
        /// <param name="ProjectType"></param>
        /// <param name="Status"></param>
        /// <returns></returns>
        protected System.Data.DataRow[] Schedules(string store_number, string store_seq, string ProjectType, ProjectStatus Status)
        {
            System.Data.DataTable table = _scheduledata.Tables["trident"];

            // create search syntax
            store_number = "str_num LIKE '" + store_number + "'";
            store_seq = "str_seq LIKE '" + store_seq + "'";
            ProjectType = "prj_typ LIKE '" + ProjectType + "'";

            // combine into one expression
            store_number += " AND " + store_seq;
            store_number += " AND " + ProjectType;
            store_number += " AND " + "proj_status = '" + Status.ToString() + "'";

            System.Data.DataRow[] rows = table.Select(store_number);
            return rows;
        }


        /// <summary>
        /// Maps str_id or contact_id and returns the rows where the id occurrs.
        /// </summary>
        /// <param name="id">the id value to search for</param>
        /// <returns></returns>
        protected System.Data.DataRow[] CrossMap(string Table, string id)
        {
            System.Data.DataRow[] rows;
            System.Data.DataTable table = _contactsdata.Tables[Table];

            return rows = table.Select(id);

        }

        protected StoresType ToStoreType(string query)
        {
            query = query.Trim();
            switch (query)
            {
                case "Expansion":
                case "expansion":
                case "EXP":
                case "Exp":
                case "exp":
                    return StoresType.Expansion;
                case "NEW":
                case "new":
                case "New":
                case "New Store":
                case "new store":
                case "New store":
                    return StoresType.New;
                case "T/O":
                case "Takeover":
                case "takeover":
                    return StoresType.Takeover;
                case "REL":
                case "Relo":
                case "relo":
                case "Relocation":
                case "relocation":
                    return StoresType.Relocation;
                default:
                    return StoresType.Undefined;
            }
        }
        protected string CreateHtmlTableRow(DataRow row, string column)
        {
            string table = String.Format(@" <TR id=""{0}_ID"">
                        <TD valign=""top"" bgcolor=""#FFFFFF"" align=""right"">
                          <DIV id=""{0}"" style=""font-weight:bold"">
                            <NOBR>{0}</NOBR>
                          </DIV>
                        </TD>
                        <TD>&nbsp;&nbsp;&nbsp;</TD>
                        <TD valign=""top"">
                          {1}
                        </TD>
                      </TR>", SqlColumnFormat(column), row[column].ToString());
            return table;
        }

        private string SqlColumnFormat(string ColumnName)
        {
            ColumnName = ColumnName.Replace('_', ' ');
            ColumnName = ColumnName.Replace("str", "Store");
            ColumnName = ColumnName.Replace("num", "Number");
            ColumnName = ColumnName.Replace("seq", "sequence");
            return ColumnName;
        }
    }
}

